package com.hr.edu.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.hr.edu.dao.EduDao;
import com.hr.edu.domain.Education;
import com.hr.user.domain.User;
import com.hr.utils.PageBean;
import com.hr.utils.TxQueryRunner;

public class EduDaoImpl implements EduDao {
	private QueryRunner qr = new TxQueryRunner();
	
	@Override
	public Education findByEduID(String edu_id) {
		// TODO Auto-generated method stub
		try {
			String sql = "select * from EDU_INFO where EDU_ID=?";
			return qr.query(sql, new BeanHandler<Education>(Education.class),edu_id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}

	@Override
	public Education findByUserID(String user_id) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Education> queryAllEducation() {
		// TODO Auto-generated method stub
		try {
			String sql = "select * from EDU_INFO";
			return qr.query(sql, new BeanListHandler<Education>(Education.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}

	@Override
	public void addEducation(Education education) {
		// TODO Auto-generated method stub
		try {
			String sql = "insert into EDU_INFO values(?,?,?,?,?)";
			Object[] params = {education.getEdu_id(),education.getEdu_name(),education.getEdu_describle(),education.getCount_number(),0};
			qr.update(sql,params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	@Override
	public void deleteEducation(Education education) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void updateEducation(Education education) {
		// TODO Auto-generated method stub
		try {
			String sql = "update EDU_INFO set EDU_NAME = ?,EDU_DESCRIBLE = ?,COUNT_NUMBER = ? where EDU_ID = ?";
			Object[] params = {education.getEdu_name(),education.getEdu_describle(),education.getCount_number(),education.getEdu_id()};
			qr.update(sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}

	@Override
	public PageBean<Education> queryAll_Page(int PageCurrent, int PageSize) {
		// TODO Auto-generated method stub
		try {
			PageBean<Education> pb = new PageBean<Education>();
			//设置当前页面页码
			pb.setPageCurrent(PageCurrent);
			//设置当前页面的页面容量
			pb.setPageSize(PageSize);
			String sql = "select Count(*) from EDU_INFO";
			Number num = (Number)qr.query(sql, new ScalarHandler());
			int rowCount = num.intValue();//获取数据库中的所有记录数
			pb.setRowCount(rowCount);
			sql = "select * from (select a1.*,rownum rn from (select * from EDU_INFO)a1 where rownum<="+PageCurrent*PageSize+")where rn >="+((PageCurrent-1)*PageSize+1);
			List<Education> list_education = qr.query(sql, new BeanListHandler<Education>(Education.class));
			pb.setBeanList(list_education);
			return pb;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public PageBean<User> queryAll_Page_User_ByEducation(int PageCurrent,int PageSize,String edu_id) {
		try {
			PageBean<User> pb = new PageBean<User>();
			//设置当前页面页码
			pb.setPageCurrent(PageCurrent);
			//设置当前页面的页面容量
			pb.setPageSize(PageSize);
			String sql = "select Count(*) from HR_USER where user_id IN (select user_id from EDU_USER where edu_id=?)";
			Number num = (Number)qr.query(sql, new ScalarHandler(),edu_id);
			int rowCount = num.intValue();//获取数据库中的所有记录数
			pb.setRowCount(rowCount);
			sql = "select * from (select a1.*,rownum rn from (select * from HR_USER where user_id IN (select user_id from EDU_USER where edu_id=?))a1 where rownum<="+PageCurrent*PageSize+")where rn >="+((PageCurrent-1)*PageSize+1);
			List<User> list_user = qr.query(sql, new BeanListHandler<User>(User.class),edu_id);
			pb.setBeanList(list_user);
			return pb;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public PageBean<Education> queryAll_Page_User(int PageCurrent, int PageSize) {
		// TODO Auto-generated method stub
		return null;
	}
	
	@Override
	public PageBean<Education> queryAll_Page_User_ByUserId(int PageCurrent, int PageSize, String user_id) {
		// TODO Auto-generated method stub
		try {
			PageBean<Education> pb = new PageBean<Education>();
			//设置当前页面页码
			pb.setPageCurrent(PageCurrent);
			//设置当前页面的页面容量
			pb.setPageSize(PageSize);
			String sql = "select Count(*) from EDU_INFO where edu_id IN (select edu_id from EDU_USER where user_id = ?)";
			Number num = (Number)qr.query(sql, new ScalarHandler(),user_id);
			int rowCount = num.intValue();//获取数据库中的所有记录数
			pb.setRowCount(rowCount);
			sql = "select * from (select a1.*,rownum rn from (select * from EDU_INFO where edu_id IN (select edu_id from EDU_USER where user_id=?))a1 where rownum<="+PageCurrent*PageSize+")where rn >="+((PageCurrent-1)*PageSize+1);
			List<Education> list_edu = qr.query(sql, new BeanListHandler<Education>(Education.class),user_id);
			pb.setBeanList(list_edu);
			return pb;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public void addEducationUser(String edu_id, String user_id) {
		// TODO Auto-generated method stub
		try {
			String sql = "insert into EDU_USER Values(?,?,?)";
			Object[] params = {edu_id,user_id,0};
			qr.update(sql, params);
			String sql_change_NOW = "update EDU_INFO set NOW_NUMBER=NOW_NUMBER+1 where EDU_ID = ?";
			qr.update(sql_change_NOW,edu_id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Override
	public String findState(Education education,User user) {
		try {
			String sql = "select state from EDU_USER where edu_id = ? and user_id = ?";
			String state = (String) qr.query(sql, new ScalarHandler(),education.getEdu_id(),user.getUser_id());
			return state;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public void deleteUserOwnEdu(String edu_id, String user_id) {
		// TODO Auto-generated method stub
		try {
			String sql = "delete EDU_USER where edu_id=? and user_id=?";
			Object[] params = {edu_id,user_id};
			qr.update(sql, params);
			String sql_change_NOW = "update EDU_INFO set NOW_NUMBER=NOW_NUMBER-1 where EDU_ID=?";
			qr.update(sql_change_NOW,edu_id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Override
	public void deleteEducation(String edu_id) {
		// TODO Auto-generated method stub
		try {
			String sql = "delete EDU_INFO where edu_id = ?";
			qr.update(sql, edu_id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public void updateEducationUser(String edu_id, String user_id) {
		// TODO Auto-generated method stub
		try {
			String sql = "update EDU_USER set state='1' where edu_id=? and user_id=?";
			Object[] params = {edu_id,user_id};
			qr.update(sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
}
